/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Modelo;

import VO.BitacoraMaquinaVO;
import VO.ConductoresVO;
import VO.MaquinariaVO;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.util.Vector;
import javax.sql.DataSource;
import utiles.SQL.GeneralOperations;
import utiles.SQL.SimpleDataSource;
import utiles.exceptions.DataBaseException;
import utiles.exceptions.InternalErrorException;

/**
 *
 * @author Jorge
 */
public class DaoMaquinaria implements DaoMaquinariaInterface {

    public boolean ingresarMaquinaria(MaquinariaVO nuevo) throws DataBaseException, InternalErrorException, SQLException {
        Connection connection = null;
        DataSource dataSource = new SimpleDataSource();
        connection = dataSource.getConnection();
        ResultSet resultSet;
        boolean result = false;
        CallableStatement callabledStatement = null;

        try {

            String queryString1 = "CALL ingresarMaquinaria(?,?,?,?,?,?,?,?,?,?)";

            callabledStatement = connection.prepareCall(queryString1, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

            callabledStatement = connection.prepareCall(queryString1);
            callabledStatement.setString("nombre", nuevo.getNombre());
            callabledStatement.setString("nomenclatura", nuevo.getNomenclatura());
            callabledStatement.setString("tipoMaq", nuevo.getTipoMaquina());
            callabledStatement.setString("capPersonas", nuevo.getCapPersonas());
            callabledStatement.setString("capEstanque", nuevo.getCapEstanque());
            callabledStatement.setString("potenciaBomba", nuevo.getPotenciaBomba());
            callabledStatement.setString("marca", nuevo.getMarca());
            callabledStatement.setString("modelo", nuevo.getModelo());
            callabledStatement.setString("anio", nuevo.getAnio());
            callabledStatement.setString("paisOrigen", nuevo.getPaisOrigen());
            //callabledStatement.setString("pass", pass);
            callabledStatement.execute();

            if (callabledStatement.getUpdateCount() > 0) {
                result = true;
            }

        } catch (SQLException e) {
            //result = null;
            // si hay un error en la conexion al driver de la BD.
            System.out.println("sql EXCEPTION ingresarDatosGenerales--->" + e.getMessage());
            throw new InternalErrorException(e);
        // llaves duplicadas
        } finally {

            GeneralOperations.closeStatement(callabledStatement);
            GeneralOperations.closeConnection(connection);
            return result;
        }


    }

    public Vector cargarNomenclaturas() throws DataBaseException, InternalErrorException, SQLException {
        Connection connection = null;
        DataSource dataSource = new SimpleDataSource();
        connection = dataSource.getConnection();
        ResultSet resultSet;
        Vector result = null;
        CallableStatement callabledStatement = null;

        try {

            String queryString1 = "CALL cargarMaquinas()";

            callabledStatement = connection.prepareCall(queryString1, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

            callabledStatement = connection.prepareCall(queryString1);

            callabledStatement.execute();

            if ((resultSet = callabledStatement.getResultSet()).isBeforeFirst() != false) {
                result = new Vector();

                while (resultSet.next()) {

                    result.add(resultSet.getString("nomenclatura"));
                }



            }


        } catch (SQLException e) {
            result = null;
            // si hay un error en la conexion al driver de la BD.
            System.out.println("sql EXCEPTION ingresarDatosGenerales--->" + e.getMessage());
            throw new InternalErrorException(e);
        // llaves duplicadas
        } finally {

            GeneralOperations.closeStatement(callabledStatement);
            GeneralOperations.closeConnection(connection);
            return result;
        }
    }

    public MaquinariaVO cargarMaquinaria(String nomenclatura) throws DataBaseException, InternalErrorException, SQLException {
        Connection connection = null;
        DataSource dataSource = new SimpleDataSource();
        connection = dataSource.getConnection();
        ResultSet resultSet;
        MaquinariaVO result = null;
        CallableStatement callabledStatement = null;

        try {

            String queryString1 = "CALL cargarMaquina(?)";

            callabledStatement = connection.prepareCall(queryString1, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

            callabledStatement = connection.prepareCall(queryString1);
            callabledStatement.setString("param", nomenclatura);

            callabledStatement.execute();

            if ((resultSet = callabledStatement.getResultSet()).isBeforeFirst() != false) {

                result = new MaquinariaVO();

                resultSet.next();


                result.setNombre(resultSet.getString("nombre"));
                result.setNomenclatura(resultSet.getString("nomenclatura"));
                result.setTipoMaquina(resultSet.getString("tipoMaq"));
                result.setAnio(resultSet.getString("anio"));
                result.setCapEstanque(resultSet.getString("capEstanque"));
                result.setCapPersonas(resultSet.getString("capPersonas"));
                result.setMarca(resultSet.getString("marca"));
                result.setModelo(resultSet.getString("modelo"));

                result.setPaisOrigen(resultSet.getString("paisOrigen"));
                result.setPotenciaBomba(resultSet.getString("potenciaBomba"));



            }


        } catch (SQLException e) {
            //result = null;
            // si hay un error en la conexion al driver de la BD.
            System.out.println("sql EXCEPTION modificarMaquina--->" + e.getMessage());
            throw new InternalErrorException(e);
        // llaves duplicadas
        } finally {

            GeneralOperations.closeStatement(callabledStatement);
            GeneralOperations.closeConnection(connection);
            return result;
        }

    }

    public static void main(String args[]) {
        DaoMaquinaria n = new DaoMaquinaria();
        MaquinariaVO i = new MaquinariaVO();
        try {
            i = n.cargarMaquinaria("4");
            System.out.println(i.getMarca());
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }

    }

    public boolean editarMaquinaria(MaquinariaVO nuevo) throws DataBaseException, InternalErrorException, SQLException {
        Connection connection = null;
        DataSource dataSource = new SimpleDataSource();
        connection = dataSource.getConnection();
        ResultSet resultSet;
        boolean result = false;
        CallableStatement callabledStatement = null;

        try {

            String queryString1 = "CALL editarMaquinaria(?,?,?,?,?,?,?,?,?,?)";

            callabledStatement = connection.prepareCall(queryString1, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

            callabledStatement = connection.prepareCall(queryString1);
            callabledStatement.setString("nombr", nuevo.getNombre());
            callabledStatement.setString("nomenclatur", nuevo.getNomenclatura());
            callabledStatement.setString("tipoMa", nuevo.getTipoMaquina());
            callabledStatement.setString("capPerson", nuevo.getCapPersonas());
            callabledStatement.setString("capEstanqu", nuevo.getCapEstanque());
            callabledStatement.setString("potenciaBomb", nuevo.getPotenciaBomba());
            callabledStatement.setString("marc", nuevo.getMarca());
            callabledStatement.setString("model", nuevo.getModelo());
            callabledStatement.setString("ani", nuevo.getAnio());
            callabledStatement.setString("paisOrige", nuevo.getPaisOrigen());
            //callabledStatement.setString("pass", pass);
            callabledStatement.execute();

            if (callabledStatement.getUpdateCount() > 0) {
                result = true;
            }

        } catch (SQLException e) {
            result = false;
            // si hay un error en la conexion al driver de la BD.
            System.out.println("sql EXCEPTION ingresarDatosGenerales--->" + e.getMessage());
            throw new InternalErrorException(e);
        // llaves duplicadas
        } finally {

            GeneralOperations.closeStatement(callabledStatement);
            GeneralOperations.closeConnection(connection);
            return result;
        }
    }

    public boolean eliminarMaquinaria(String nomenclatura) throws DataBaseException, InternalErrorException, SQLException {
        Connection connection = null;
        DataSource dataSource = new SimpleDataSource();
        connection = dataSource.getConnection();
        ResultSet resultSet;
        boolean result = false;
        CallableStatement callabledStatement = null;

        try {

            String queryString1 = "CALL eliminarMaquina(?)";

            callabledStatement = connection.prepareCall(queryString1, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

            callabledStatement = connection.prepareCall(queryString1);

            callabledStatement.setString("nomenclaturaParam", nomenclatura);

            callabledStatement.execute();

            if (callabledStatement.getUpdateCount() > 0) {
                result = true;
            }

        } catch (SQLException e) {
            result = false;
            // si hay un error en la conexion al driver de la BD.
            System.out.println("sql EXCEPTION ingresarDatosGenerales--->" + e.getMessage());
            throw new InternalErrorException(e);
        // llaves duplicadas
        } finally {

            GeneralOperations.closeStatement(callabledStatement);
            GeneralOperations.closeConnection(connection);
            return result;
        }
    }

    public Vector listarMaquinarias() throws DataBaseException, InternalErrorException, SQLException {
        Connection connection = null;
        DataSource dataSource = new SimpleDataSource();
        connection = dataSource.getConnection();
        ResultSet resultSet;
        Vector result = null;
        MaquinariaVO aux = null;

        CallableStatement callabledStatement = null;

        try {

            String queryString1 = "CALL listarMaquinas()";

            callabledStatement = connection.prepareCall(queryString1, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

            callabledStatement = connection.prepareCall(queryString1);
           

            callabledStatement.execute();

            if ((resultSet = callabledStatement.getResultSet()).isBeforeFirst() != false) {

                result = new Vector();

                
                while (resultSet.next()) {

                    aux = new MaquinariaVO();
                    aux.setNombre(resultSet.getString("nombre"));
                    aux.setNomenclatura(resultSet.getString("nomenclatura"));
                    aux.setTipoMaquina(resultSet.getString("tipoMaq"));
                    aux.setAnio(resultSet.getString("anio"));
                    aux.setCapEstanque(resultSet.getString("capEstanque"));
                    aux.setCapPersonas(resultSet.getString("capPersonas"));
                    aux.setMarca(resultSet.getString("marca"));
                    aux.setModelo(resultSet.getString("modelo"));
                    aux.setPaisOrigen(resultSet.getString("paisOrigen"));
                    aux.setPotenciaBomba(resultSet.getString("potenciaBomba"));

                    result.add(aux);
                }


            }


        } catch (SQLException e) {
            //result = null;
            // si hay un error en la conexion al driver de la BD.
            System.out.println("sql EXCEPTION modificarMaquina--->" + e.getMessage());
            throw new InternalErrorException(e);
        // llaves duplicadas
        } finally {

            GeneralOperations.closeStatement(callabledStatement);
            GeneralOperations.closeConnection(connection);
            return result;
        }
    }

    public Vector bitacoraMaquinaria(String nom) throws DataBaseException, InternalErrorException, SQLException {

        Connection connection = null;
        DataSource dataSource = new SimpleDataSource();
        connection = dataSource.getConnection();
        ResultSet resultSet;
        Vector result = null;
        BitacoraMaquinaVO aux = null;

        CallableStatement callabledStatement = null;

        try {

            String queryString1 = "CALL bitacoraMaquina(?)";

            callabledStatement = connection.prepareCall(queryString1, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

            callabledStatement = connection.prepareCall(queryString1);
           callabledStatement.setString("nomP", nom);

            callabledStatement.execute();

            if ((resultSet = callabledStatement.getResultSet()).isBeforeFirst() != false) {

                result = new Vector();


                while (resultSet.next()) {

                    aux = new BitacoraMaquinaVO();
                    aux.setMaquina(resultSet.getString("maquina"));
                    aux.setTipo(resultSet.getString("tipo"));
                    aux.setFecha(resultSet.getString("fecha"));

                    result.add(aux);
                }


            }


        } catch (SQLException e) {
            //result = null;
            // si hay un error en la conexion al driver de la BD.
            System.out.println("sql EXCEPTION modificarMaquina--->" + e.getMessage());
            throw new InternalErrorException(e);
        // llaves duplicadas
        } finally {

            GeneralOperations.closeStatement(callabledStatement);
            GeneralOperations.closeConnection(connection);
            return result;
        }
    }

    public Vector cargarConductores(String maquinaria) throws DataBaseException, InternalErrorException, SQLException {
        Connection connection = null;
        DataSource dataSource = new SimpleDataSource();
        connection = dataSource.getConnection();
        ResultSet resultSet;
        Vector resultado = new Vector();
        CallableStatement callabledStatement = null;

        try {

            String queryString1 = "CALL cargarConductores(?)";

            callabledStatement = connection.prepareCall(queryString1, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

            callabledStatement = connection.prepareCall(queryString1);
            callabledStatement.setString("maquina", maquinaria);

            callabledStatement.execute();

            if ((resultSet = callabledStatement.getResultSet()).isBeforeFirst() != false) {
                while (resultSet.next())
                {
                    resultado.add(resultSet.getString("rut"));
                }
            }
        } catch (SQLException e) {
            resultado = null;
            System.out.println("sql EXCEPTION cargar conductores--->" + e.getMessage());
            throw new InternalErrorException(e);
        // llaves duplicadas
        } finally {
            GeneralOperations.closeStatement(callabledStatement);
            GeneralOperations.closeConnection(connection);
            return resultado;
        }
    }

    public Vector cargarNoConductores(String maquinaria) throws DataBaseException, InternalErrorException, SQLException {
        Connection connection = null;
        DataSource dataSource = new SimpleDataSource();
        connection = dataSource.getConnection();
        ResultSet resultSet;
        Vector resultado = new Vector();
        CallableStatement callabledStatement = null;

        try {

            String queryString1 = "CALL cargarNoConductores(?)";

            callabledStatement = connection.prepareCall(queryString1, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

            callabledStatement = connection.prepareCall(queryString1);
            callabledStatement.setString("maquina", maquinaria);

            callabledStatement.execute();

            if ((resultSet = callabledStatement.getResultSet()).isBeforeFirst() != false) {
                while (resultSet.next())
                {
                    resultado.add(resultSet.getString("rut"));
                }
            }
        } catch (SQLException e) {
            resultado = null;
            System.out.println("sql EXCEPTION cargar No conductores--->" + e.getMessage());
            throw new InternalErrorException(e);
        // llaves duplicadas
        } finally {
            GeneralOperations.closeStatement(callabledStatement);
            GeneralOperations.closeConnection(connection);
            return resultado;
        }
    }

    public boolean registrarConductores(ConductoresVO listaCond) throws DataBaseException, InternalErrorException, SQLException
    {
        Connection connection = null;
        DataSource dataSource = new SimpleDataSource();
        connection = dataSource.getConnection();
        ResultSet resultSet;
        boolean resultado = false;
        CallableStatement callabledStatement = null;

        try {
            String maquinariaNomen = listaCond.getMaquinaria();

            String queryString1 = "CALL limpiarConductores(?)";

            callabledStatement = connection.prepareCall(queryString1, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            callabledStatement = connection.prepareCall(queryString1);
            callabledStatement.setString("maquinariaNomen", maquinariaNomen);

            callabledStatement.execute();


            for (int i = 0; i < listaCond.getConductores().size(); i++) {

                queryString1 = "CALL registrarConductor(?,?)";

                callabledStatement = connection.prepareCall(queryString1, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
                callabledStatement = connection.prepareCall(queryString1);
                callabledStatement.setString("maquinariaNomen", maquinariaNomen);
                callabledStatement.setString("rutC", listaCond.getConductores().get(i).toString());

                callabledStatement.execute();
            }

            if (callabledStatement.getUpdateCount() > 0) {
                resultado = true;
            }

        } catch (SQLException e) {
            System.out.println("sql EXCEPTION registrar Conductores--->" + e.getMessage());
            throw new InternalErrorException(e);
        // llaves duplicadas
        } finally {
            GeneralOperations.closeStatement(callabledStatement);
            GeneralOperations.closeConnection(connection);
            return resultado;
        }
    }
}
